﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

namespace CashierSystem.remindManager
{
    class UserRemindManager:DbConnection
    {
        public static void saveRemind(string remindContent, string remindDate,string userId)
        {
            string sql = "insert into t_remind(remindContent,remindDate,userId) values('" + remindContent + "','" + remindDate + "','" + userId + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }
        public static void loadRemind(ListView lv1,string userId)
        {
            string sql = "SELECT remindContent, remindDate FROM t_remind WHERE (DATEDIFF(dd, GETDATE(), DATEADD(yy, DATEDIFF(yy, remindDate, GETDATE()) + 1, remindDate))";
            sql += " BETWEEN 0 AND 7) OR (DATEDIFF(dd, GETDATE(), DATEADD(yy, DATEDIFF(yy, remindDate, GETDATE()),  remindDate)) BETWEEN 0 AND 7)  and userId = '" + userId + "' ORDER BY remindDate";

            lv1.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["remindContent"].ToString();
                    string date = dbReader["remindDate"].ToString();
                    DateTime d = DateTime.Parse(date);
                    string sd = d.ToString("yyyy-MM-dd");//这是一个字符串转换为日期格式时很好用的三句话
                    li.SubItems.Add(sd);
                    lv1.Items.Add(li);
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void loadAllRemind(ListView lv,string startDate,string endDate)
        {
            string sql = "SELECT t_remind.id, t_remind.remindContent, t_remind.remindDate, t_remind.status,t_user.name ";
            sql += "FROM t_remind inner join t_user on t_user.userId = t_remind.userId WHERE (1 = 1)";

            if (!startDate.Equals(""))
            {
                sql += " and remindDate >= '" + startDate + "'";//convert(VARCHAR(10),
            }
            if (!endDate.Equals(""))
            {
                sql += " and remindDate <= '" + endDate + "'";
            }
            sql += "order by remindDate desc";

            lv.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["id"].ToString();
                    li.SubItems.Add(dbReader["remindContent"].ToString());
                    string date = dbReader["remindDate"].ToString();
                    DateTime d = DateTime.Parse(date);
                    string sd = d.ToString("yyyy-MM-dd");//这是一个字符串转换为日期格式时很好用的三句话
                    li.SubItems.Add(sd);
                    string status = dbReader["status"].ToString();
                    if (status == "0")
                    {
                        li.SubItems.Add("未完成");
                    }
                    else 
                    {
                        li.SubItems.Add("已完成");
                    }
                    li.SubItems.Add(dbReader["name"].ToString());
                    lv.Items.Add(li);
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
 
        }

        public static void deleteRemind(string id)
        {
            string sql = "delete from t_remind where id = '" + id +"'";
            try
            {
                conn.Close();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
 
        }

        public static void updateRemind(string id,string content,string remindDate)
        {

            string sql = "update t_remind set remindContent = '" + content + "',remindDate = '" + remindDate +"' where id = '" + id +"'";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

    }
}
